#!/bin/bash
APP=edu

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi
dws_video_audition_play_nd="
insert overwrite table dws_video_audition_play_nd partition(dt='$do_date')
select
       course_id,
       course_name,
       subject_id,
       subject_name,
       category_id,
       category_name,
       sum(user_count_1d) user_count_7d,
       sum(audition_user_sum_1d) audition_user_sum_7d
from dws_video_audition_play_1d
where dt>date_add('$do_date',-6)
and dt<='$do_date'
group by course_id, course_name, subject_id, subject_name, category_id, category_name;"
dws_study_course_avg_stars_nd="
insert overwrite table dws_study_course_avg_stars_nd partition(dt='$do_date')
select
    course_id,
    7d_total_review_stars/7d_cnt as 7d_avg_stars,
    7d_user_cnt,
    7d_5_stars_cnt/7d_cnt as 7d_5_stars_rate,
    30d_total_review_stars/30d_cnt as 30d_avg_stars,
    30d_user_cnt,
    30d_5_stars_cnt/30d_cnt as 30d_5_stars_tate
from (
    select
        course_id,
        sum(if(dt > date_add('$do_date',-7),1,0)) as 7d_cnt,
        sum(if(dt > date_add('$do_date',-7),review_stars,0)) 7d_total_review_stars,
        count(distinct if(dt > date_add('$do_date',-7),user_id,null)) as 7d_user_cnt,
        sum(if(review_stars=5 and dt > date_add('$do_date',-7),1,0)) 7d_5_stars_cnt,
        count(*) as 30d_cnt,
        sum(review_stars) 30d_total_review_stars,
        count(distinct if(dt > date_add('$do_date',-30),user_id,null)) as 30d_user_cnt,
        sum(if(review_stars=5 and dt > date_add('$do_date',-30),1,0)) 30d_5_stars_cnt
    from dwd_review_info_inc
    where dt>=date_add('$do_date',-29)
    and dt<='$do_date'
    group by course_id
)t1;"
dws_traffic_source_order_payment_nd="
INSERT OVERWRITE TABLE dws_traffic_source_order_payment_nd partition (dt='$do_date')
SELECT
    sc,
    source_site,
    sum(`if`(dt > date_sub('$do_date',7),total_amount_1d,0)) total_amount_7d,
    sum(total_amount_1d) total_amount_30d,
    sum(`if`(dt > date_sub('$do_date',7),user_count_1d,0)) user_count_7d,
    sum(user_count_1d) user_count_30d,
    sum(`if`(dt > date_sub('$do_date',7),order_count_1d,0)) order_count_7d,
    sum(order_count_1d) order_count_30d
FROM dws_traffic_source_order_payment_1d
WHERE dt > date_sub('$do_date',30) 
GROUP BY sc, source_site;"
case $1 in
    "dws_video_audition_play_nd" )
        hive -e "$dws_video_audition_play_nd"
    ;;
    "dws_study_course_avg_stars_nd" )
        hive -e "$dws_study_course_avg_stars_nd"
    ;;
	"dws_traffic_source_order_payment_nd")
	hive -e "dws_traffic_source_order_payment_nd"
    "all" )
        hive -e "$dws_traffic_source_order_payment_nd
		$dws_study_course_avg_stars_nd
		$dws_traffic_source_order_payment_nd"
    ;;
esac

